﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
//using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SYS_DAL
{
    public class ScriptTypeDAL
    {
        public ScriptTypeDAL(string constr)
        {
            SQLiteHelper.connectionString = constr;
        }
        #region 动态 取表里字段的类型和长度,此处没有动态用到connstr,是默认的！by/文少
        /*public System.Data.DataTable GetTabType(string tabnale)
        {
            try
            {
                string sql = "SELECT * FROM INFORMATION_SCHEMA.columns WHERE TABLE_NAME='" + tabnale.ToUpper() + "'";
                OpenConn();
                return ReturnDataSet(sql);
            }
            catch (Exception er)
            {
                Log.INIIO.saveLogInf("GetTabType异常:" + er.Message);
                return null;
            }
        }*/
        public System.Data.DataTable GetTabType(string tabnale)
        {
            try
            {
                string sql = "PRAGMA table_info('" + tabnale.ToUpper() + "')";
                OpenConn();
                return ReturnDataSet(sql);
            }
            catch (Exception er)
            {
                Log.INIIO.saveLogInf("GetTabType异常:" + er.Message);
                return null;
            }
        }
        #endregion
        #region 执行SQL语句，返回数据到DataSet中
        /// 执行SQL语句，返回数据到DataSet中
        /// sql语句
        /// 自定义返回的DataSet表名
        /// 返回DataSet
        public DataTable ReturnDataSet(string sql)
        {
            try
            {
                DataTable dt = new DataTable();
                dt = SQLiteHelper.GetDataTable(sql);
                return dt;
            }
            catch (Exception er)
            {
                Log.INIIO.saveLogInf("ReturnDataSet异常:" + er.Message);
                return null;
            }

        }
        #endregion
        public DataTable GetTableDatatable(string TableName, string ht_Where, string order = "")
        {
            try
            {
                DataTable dt = new DataTable();
                string sql = GetContentSqlbyHt(TableName, ht_Where, order);
                Log.INIIO.saveLogInf(sql);
                dt = SQLiteHelper.GetDataTable(sql);
                return dt;
            }
            catch (Exception er)
            {
                Log.INIIO.saveLogInf("GetTableDatatable异常:" + er.Message);
                return null;
            }

        }
        //＝＝用hashTable对数据库进行insert,update,del操作,注意此时只能用默认的数据库连接"connstr"
        #region 根据表名及哈稀表自动插入数据库 用法：Insert("test",ht)
        public int Insert(string TableName, Hashtable ht)
        {
            try
            {
                SqlParameter[] Parms = new SqlParameter[ht.Count];
                IDictionaryEnumerator et = ht.GetEnumerator();
                DataTable dt = GetTabType(TableName);

                DbType otype;

                int size = 0;
                int i = 0;
                while (et.MoveNext()) // 作哈希表循环
                {
                    GetoType(et.Key.ToString(), dt, out otype, out size);
                    if (otype == DbType.DateTime)
                    {
                        DateTime temp = DateTime.Now;
                        SqlParameter op = MakeParam(":" + et.Key.ToString(),
                            otype,
                            size,
                            DateTime.TryParse(et.Value.ToString(), out temp) ?
                            temp.ToString("yyyy-MM-dd HH:mm:ss") :
                            DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                        Parms[i] = op; // 添加SqlParameter对象
                    }
                    else if (otype == DbType.Date)
                    {
                        DateTime temp = DateTime.Now;
                        SqlParameter op = MakeParam(":" + et.Key.ToString(),
                            otype,
                            size,
                            DateTime.TryParse(et.Value.ToString(), out temp) ?
                            temp.ToString("yyyy-MM-dd") :
                            DateTime.Now.ToString("yyyy-MM-dd"));
                        Parms[i] = op; // 添加SqlParameter对象
                    }
                    else
                    {
                        SqlParameter op = MakeParam(":" + et.Key.ToString(), otype, size, et.Value.ToString());
                        Parms[i] = op; // 添加SqlParameter对象
                    }
                    i = i + 1;
                }
                string str_Sql = GetInsertSqlbyHt(TableName, ht); // 获得插入sql语句
                int val = SQLiteHelper.ExecuteNonQuery(str_Sql);
                Log.INIIO.saveLogInf(str_Sql);
                return val;
            }
            catch (Exception er)
            {
                Log.INIIO.saveLogInf("Insert异常:" + er.Message);
                return 0;
            }
        }
        #endregion

        #region 根据相关条件对数据库进行更新操作 用法：Update("test","Id=:Id",ht); 
        public int Update(string TableName, string ht_Where, Hashtable ht)
        {
            try
            {
                SqlParameter[] Parms = new SqlParameter[ht.Count];
                IDictionaryEnumerator et = ht.GetEnumerator();
                DataTable dt = GetTabType(TableName);
                DbType otype;
                int size = 0;
                int i = 0;
                // 作哈希表循环
                while (et.MoveNext())
                {
                    GetoType(et.Key.ToString().ToUpper(), dt, out otype, out size);

                    if (otype == DbType.DateTime)
                    {
                        DateTime temp = DateTime.Now;
                        SqlParameter op = MakeParam("@" + et.Key.ToString(), otype, size,
                            DateTime.TryParse(et.Value.ToString(), out temp) ?
                            temp.ToString("yyyy-MM-dd HH:mm:ss") :
                            DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                        Parms[i] = op; // 添加SqlParameter对象
                    }
                    else if (otype == DbType.Date)
                    {
                        DateTime temp = DateTime.Now;
                        SqlParameter op = MakeParam("@" + et.Key.ToString(), otype, size,
                            DateTime.TryParse(et.Value.ToString(), out temp) ?
                            temp.ToString("yyyy-MM-dd") :
                            DateTime.Now.ToString("yyyy-MM-dd"));
                        Parms[i] = op; // 添加SqlParameter对象
                    }
                    else
                    {
                        SqlParameter op = MakeParam("@" + et.Key.ToString(), otype, size, et.Value.ToString());
                        Parms[i] = op; // 添加SqlParameter对象
                    }
                    i = i + 1;
                }
                string str_Sql = GetUpdateSqlbyHt(TableName, ht_Where, ht); // 获得插入sql语句
                Log.INIIO.saveLogInf(str_Sql);
                int val = SQLiteHelper.ExecuteNonQuery(str_Sql, Parms);
                return val;

            }
            catch (Exception er)
            {
                Log.INIIO.saveLogInf("Update异常:" + er.Message);
                return 0;
            }
        }
        #endregion

        #region 根据相关条件对数据库进行更新操作 用法：Update("test","Id=:Id",ht); 
        public int InsertOrUpdate(string TableName, string ht_Where, Hashtable ht)
        {
            try
            {
                if (ht_Where != "" && GetRecordCount("select * from " + TableName + " where " + ht_Where) > 0)
                {
                    return Update(TableName, ht_Where, ht);
                }
                else
                {
                    return Insert(TableName, ht);
                }

            }
            catch (Exception er)
            {
                Log.INIIO.saveLogInf("InsertOrUpdate异常:" + er.Message);
                return 0;
            }
        }
        #endregion

        #region 根据相关条件对数据库进行更新操作 用法：Update("test","Id=:Id",ht); 
        public int Delete(string TableName, string ht_Where)
        {
            try
            {
                string str_Sql = "delete from " + TableName + " where " + ht_Where;
                int val = SQLiteHelper.ExecuteNonQuery(str_Sql);
                return val;

            }
            catch (Exception er)
            {
                Log.INIIO.saveLogInf("Delete:" + er.Message);
                return 0;
            }
        }
        #endregion

        #region del操作,注意此处条件个数与hash里参数个数应该一致 用法：Del("test","Id=:Id",ht)
        public int Del(string TableName, string ht_Where, Hashtable ht)
        {
            try
            {
                SqlParameter[] Parms = new SqlParameter[ht.Count];
                IDictionaryEnumerator et = ht.GetEnumerator();
                DataTable dt = GetTabType(TableName);
                DbType otype;
                int i = 0;
                int size = 0;
                // 作哈希表循环
                while (et.MoveNext())
                {
                    GetoType(et.Key.ToString().ToUpper(), dt, out otype, out size);
                    SqlParameter op = MakeParam(":" + et.Key.ToString(), et.Value.ToString());
                    Parms[i] = op; // 添加SqlParameter对象
                    i = i + 1;
                }
                string str_Sql = GetDelSqlbyHt(TableName, ht_Where, ht); // 获得删除sql语句
                int val = SQLiteHelper.ExecuteNonQuery(str_Sql, Parms);
                return val;
            }
            catch (Exception er)
            {
                Log.INIIO.saveLogInf("Del异常:" + er.Message);
                return 0;
            }
        }
        #endregion

        //　＝＝＝＝＝＝＝＝上面三个操作的内部调用函数＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

        #region 根据哈稀表及表名自动生成相应insert语句(参数类型的)
        /// 根据哈稀表及表名自动生成相应insert语句 
        /// 要插入的表名
        /// 哈稀表
        /// 返回sql语句
        public static string GetInsertSqlbyHt(string TableName, Hashtable ht)
        {
            string str_Sql = "";
            int i = 0;
            int ht_Count = ht.Count; // 哈希表个数
            IDictionaryEnumerator myEnumerator = ht.GetEnumerator();
            string before = "";
            string behide = "";
            while (myEnumerator.MoveNext())
            {
                if (i == 0)
                {
                    before = "(" + myEnumerator.Key;
                }
                else if (i + 1 == ht_Count)
                {
                    before = before + "," + myEnumerator.Key + ")";
                }
                else
                {
                    before = before + "," + myEnumerator.Key;
                }
                i = i + 1;
            }
            myEnumerator = ht.GetEnumerator();
            i = 0;
            while (myEnumerator.MoveNext())
            {
                if (i == 0)
                {
                    behide = "('" + myEnumerator.Value + "'";
                }
                else if (i + 1 == ht_Count)
                {
                    behide = behide + ",'" + myEnumerator.Value + "')";
                }
                else
                {
                    behide = behide + ",'" + myEnumerator.Value + "'";
                }
                i = i + 1;
            }
            behide = behide.Replace("'DateTimeS", "to_date('").Replace("DateTimeE'", "','yyyy-MM-dd hh24:mi:ss')");
            behide = " Values" + behide;
            str_Sql = "Insert into " + TableName + before + behide;
            return str_Sql;
        }
        #endregion

        #region 根据表名，where条件，哈稀表自动生成更新语句(参数类型的)
        public static string GetUpdateSqlbyHt(string Table, string ht_Where, Hashtable ht)
        {
            string str_Sql = "";
            int i = 0;
            int ht_Count = ht.Count; // 哈希表个数
            IDictionaryEnumerator myEnumerator = ht.GetEnumerator();
            while (myEnumerator.MoveNext())
            {
                if (i == 0)
                {
                    if (ht_Where.ToString().ToLower().IndexOf((myEnumerator.Key + "=@" + myEnumerator.Key).ToLower()) == -1)
                    {
                        str_Sql = myEnumerator.Key + "=@" + myEnumerator.Key;
                    }
                }
                else
                {
                    if (ht_Where.ToString().ToLower().IndexOf((":" + myEnumerator.Key + " ").ToLower()) == -1)
                    {
                        str_Sql = str_Sql + "," + myEnumerator.Key + "=@" + myEnumerator.Key;
                    }
                }
                i = i + 1;
            }
            if (ht_Where == null || ht_Where.Replace(" ", "") == "") // 更新时候没有条件
            {
                str_Sql = "update " + Table + " set " + str_Sql;
            }
            else
            {
                str_Sql = "update " + Table + " set " + str_Sql + " where " + ht_Where;
            }
            str_Sql = str_Sql.Replace("set ,", "set ").Replace("update ,", "update ");
            return str_Sql;
        }
        #endregion

        #region 根据表名，where条件，哈稀表自动生成查询语句(参数类型的)
        public static string GetContentSqlbyHt(string Table, string ht_Where, string order = "")
        {
            string str_Sql = "";

            if (ht_Where == null || ht_Where.Replace(" ", "") == "") // 更新时候没有条件
            {
                str_Sql = "select * from " + Table;
            }
            else
            {
                str_Sql = "select * from " + Table + " where " + ht_Where;
            }
            if (!string.IsNullOrEmpty(order))
            {
                str_Sql += " " + order;
            }
            return str_Sql;
        }
        #endregion
        #region 根据表名，where条件，哈稀表自动生成del语句(参数类型的)
        public static string GetDelSqlbyHt(string Table, string ht_Where, Hashtable ht)
        {
            string str_Sql = "";
            int i = 0;

            int ht_Count = ht.Count; // 哈希表个数
            IDictionaryEnumerator myEnumerator = ht.GetEnumerator();
            while (myEnumerator.MoveNext())
            {
                if (i == 0)
                {
                    if (ht_Where.ToString().ToLower().IndexOf((myEnumerator.Key + "=:" + myEnumerator.Key).ToLower()) == -1)
                    {
                        str_Sql = myEnumerator.Key + "=:" + myEnumerator.Key;
                    }
                }
                else
                {
                    if (ht_Where.ToString().ToLower().IndexOf((":" + myEnumerator.Key + " ").ToLower()) == -1)
                    {
                        str_Sql = str_Sql + "," + myEnumerator.Key + "=:" + myEnumerator.Key;
                    }
                }
                i = i + 1;
            }
            if (ht_Where == null || ht_Where.Replace(" ", "") == "") // 更新时候没有条件
            {
                str_Sql = "Delete from " + Table;
            }
            else
            {
                str_Sql = "Delete from " + Table + " where " + ht_Where;
            }
            return str_Sql;
        }
        #endregion

        #region 生成oracle参数
        /// 
        /// 生成oracle参数
        /// 字段名
        /// 数据类型
        /// 数据大小
        /// 值
        public static SqlParameter MakeParam(string ParamName, DbType otype, int size, Object Value)
        {
            SqlParameter para = new SqlParameter(ParamName, Value);
            para.DbType = otype;
            para.Size = size;
            return para;
        }
        #endregion
        #region 生成oracle参数
        public static SqlParameter MakeParam(string ParamName, string Value)
        {
            return new SqlParameter(ParamName, Value);
        }
        #endregion
        #region 根据表结构字段的类型和长度拼装oracle sql语句参数
        public static void GetoType(string key, DataTable dt, out DbType otype, out int size)
        {
            try
            {
                DataView dv = dt.DefaultView;
                dv.RowFilter = "name='" + key + "'";
                string fType = dv[0]["type"].ToString().Split('(')[0];
                switch (fType.ToUpper())
                {
                    case "DATE":
                        otype = DbType.Date;
                        size = 50;// int.Parse(dv[0]["data_length"].ToString());
                        break;
                    case "DATETIME":
                        otype = DbType.DateTime;
                        size = 50;// int.Parse(dv[0]["data_length"].ToString());
                        break;
                    case "CHAR":
                        otype = DbType.Byte;
                        size = int.Parse(dv[0]["type"].ToString().Split('(')[1].Split(')')[0]);
                        break;
                    case "INT":
                        otype = DbType.Int32;
                        size = int.Parse(dv[0]["type"].ToString().Split('(')[1].Split(')')[0]);
                        break;
                    case "INT64":
                        otype = DbType.Int64;
                        size = int.Parse(dv[0]["type"].ToString().Split('(')[1].Split(')')[0]);
                        break;
                    case "VARCHAR":
                        otype = DbType.String;
                        size = int.Parse(dv[0]["type"].ToString().Split('(')[1].Split(')')[0]);
                        break;
                    default:
                        otype = DbType.String;
                        size = 100;
                        break;
                }
            }
            catch
            {
                otype = DbType.String;
                size = 100;
            }
        }
        #endregion


        #region 打开数据库 
        /// 打开数据库
        public void OpenConn()
        {

        }
        #endregion
        #region 关闭数据库联接 
        /// 关闭数据库联接
        public void CloseConn()
        {
        }
        #endregion




        #region 执行SQL语句，返回记录总数数
        /// 执行SQL语句，返回记录总数数
        /// sql语句
        /// 返回记录总条数
        public int GetRecordCount(string sql)
        {
            int recordCount = 0;
            DataTable dt = SQLiteHelper.GetDataTable(sql);
            return dt.Rows.Count;
        }
        #endregion



    }

    public class SQLiteHelper
    {
        public static string connectionString = "";
        //生成连接字符串
        private static string CreateConnectionString()
        {
            return connectionString;
        }

        /// <summary>
        /// 对插入到数据库中的空值进行处理
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>
        public static object ToDbValue(object value)
        {
            if (value == null)
            {
                return DBNull.Value;
            }
            else
            {
                return value;
            }
        }

        /// <summary>
        /// 对从数据库中读取的空值进行处理
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>
        public static object FromDbValue(object value)
        {
            if (value == DBNull.Value)
            {
                return null;
            }
            else
            {
                return value;
            }
        }

        /// <summary>
        /// 执行非查询的数据库操作
        /// </summary>
        /// <param name="sqlString">要执行的sql语句</param>
        /// <param name="parameters">参数列表</param>
        /// <returns>返回受影响的条数</returns>
        public static int ExecuteNonQuery(string sqlString, params SqlParameter[] parameters)
        {
            string connectionString = CreateConnectionString();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sqlString;
                    foreach (SqlParameter parameter in parameters)
                    {
                        cmd.Parameters.Add(parameter);
                    }
                    return cmd.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// 执行查询并返回查询结果第一行第一列
        /// </summary>
        /// <param name="sqlString">SQL语句</param>
        /// <param name="sqlparams">参数列表</param>
        /// <returns></returns>
        public static object ExecuteScalar(string sqlString, params SqlParameter[] parameters)
        {
            string connectionString = CreateConnectionString();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sqlString;
                    foreach (SqlParameter parameter in parameters)
                    {
                        cmd.Parameters.Add(parameter);
                    }
                    return cmd.ExecuteScalar();
                }
            }
        }

        /// <summary>
        /// 查询多条数据
        /// </summary>
        /// <param name="sqlString">SQL语句</param>
        /// <param name="parameters">参数列表</param>
        /// <returns>返回查询的数据表</returns>
        public static DataTable GetDataTable(string sqlString, params SqlParameter[] parameters)
        {
            string connectionString = CreateConnectionString();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sqlString;
                    foreach (SqlParameter parameter in parameters)
                    {
                        cmd.Parameters.Add(parameter);
                    }
                    DataSet ds = new DataSet();
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    adapter.Fill(ds);
                    return ds.Tables[0];
                }
            }
        }
        /// <summary>
        /// 创建表
        /// </summary>
        /// <param name="dbPath">指定数据库文件</param>
        /// <param name="tableName">表名称</param>
        static public void NewTable(string tableName)
        {
            string connectionString = CreateConnectionString();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "CREATE TABLE " + tableName + "(Name varchar,Team varchar, Number varchar)"; ;
                    cmd.ExecuteNonQuery();
                }
                conn.Close();
            }
        }


        static public DataTable GetTableInf(string tableName)
        {

            string connectionString = CreateConnectionString();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = string.Format("PRAGMA table_info({0})", tableName);
                    DataSet ds = new DataSet();
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    adapter.Fill(ds);
                    return ds.Tables[0];
                }
            }
        }
    }
}
